<?php
//Config : Les informations personnels de l'instance (log, pass, etc)
require("../include/config.php");

//API Fonctions : les fonctions fournis de base par l'API
require("../API/php/fonctions.php");

//Header établie la connection à la base $connection
require("../API/php/header.php");

//Fonctions : Fonctions personnelles de l'instance
require("../php/fonctions.php");

//Mode debug
$modeDebug = false;

//Public ou privé (clé obligatoire)
$modePublic = true;

//Mode de sortie text,json,xml,csv
//pour xml et csv $object_retour->data["resultat"] doit contenir qu'un est unique array
$modeSortie = "json";

//Liens de test
// phpsql/getPb.php?milis=123450&ctrl=ok

//Définition des entrants
$arrayInput = array(
    "ctrl" => null
);

//Récupération des entrants
$arrayValeur = recupInput($arrayInput);

//Object retour minima
// $object_retour->strErreur string
// $object_retour->data  string
// $object_retour->statut  string
    
$connection->exec("SET CHARACTER SET utf8");

//--------------------------------------------------------------------------
$strSql = "CREATE TEMPORARY TABLE `".$prefixTable."tmp_list_defect` AS
    SELECT DISTINCT SUBSTRING(a.commentaire, LOCATE('D', a.commentaire), 4) as 'id_defect'
    FROM `tab_suivihebdo_enreg` a
    WHERE 1=1
    AND a.`date_saisi` < CURDATE()
    AND a.`commentaire` REGEXP 'D[0-9][0-9][0-9]'
;";
$req = $connection->prepare($strSql);

$tmp_list_defect = "";
if($req->execute()){
    $tmp_list_defect = "ok"; 
}else{
    die('Erreur SQL:'.print_r($req->errorInfo(), true)." (".$strSql.")");
}
$req->closeCursor();

//--------------------------------------------------------------------------
$strSql = "CREATE TEMPORARY TABLE `".$prefixTable."tmp_datas` AS
    SELECT b.`id_defect`, ROUND((SUM(a.`charge`)/10*8),2) as 'effort', IF(c.`id` is NULL, 0, 1) as 'inKpi', c.`actif`, c.`raisedDate`, c.`resolvedDate`
    FROM `tab_suivihebdo_enreg` a, `tmp_list_defect` b
            LEFT OUTER JOIN `tab_kpi_pb` c
            ON b.`id_defect` = c.`pbNum`
    WHERE 1=1
    AND a.`date_saisi` < CURDATE()
    AND a.commentaire like concat('%',b.id_defect,'%')
    GROUP BY b.`id_defect`
;";
$req = $connection->prepare($strSql);

$tmp_datas = "";
if($req->execute()){
    $tmp_datas = "ok"; 
}else{
    die('Erreur SQL:'.print_r($req->errorInfo(), true)." (".$strSql.")");
}
$req->closeCursor();

//--------------------------------------------------------------------------
$strSql = "UPDATE `".$prefixTable."tab_kpi_pb` a
        LEFT JOIN `".$prefixTable."tmp_datas` b
        ON a.`pbNum` = b.`id_defect`
        AND b.`inKpi` = 1
        AND b.`actif` = 1
    SET a.`effortSpend` = b.`effort`,
    a.`turnAround` = DATEDIFF(b.`resolvedDate`,b.`raisedDate`)
;";
$req = $connection->prepare($strSql);

$UPDATE = "";
if($req->execute()){
    $UPDATE = "ok"; 
}else{
    die('Erreur SQL:'.print_r($req->errorInfo(), true)." (".$strSql.")");
}
$req->closeCursor();

//--------------------------------------------------------------------------
$strSql = "INSERT INTO `".$prefixTable."tab_kpi_pb`
    (`pbNum`, `effortSpend`, `actif`) 
    SELECT a.`id_defect`, a.`effort`, 1
    FROM `".$prefixTable."tmp_datas` a
    WHERE 1=1
    AND a.`inKpi` = 0
;";
$req = $connection->prepare($strSql);

$INSERT = "";
if($req->execute()){
    $INSERT = "ok"; 
}else{
    die('Erreur SQL:'.print_r($req->errorInfo(), true)." (".$strSql.")");
}
$req->closeCursor();

//--------------------------------------------------------------------------
$strSql = "SELECT `id`, `pbNum`, `descr`, `raisedDate`, `resolvedDate`, `effortSpend`, `costSpend`, `revenue`, `benefits`, `descrBenefits`, `turnAround`, `actif`
    FROM `".$prefixTable."tab_kpi_pb`
    WHERE `actif` = 1
";

// On envois la requète
$select = $connection->prepare($strSql);

if($select->execute()){
    // On indique que nous utiliserons les résultats en tant qu'objet
    $select->setFetchMode(PDO::FETCH_OBJ);

    // On transforme les résultats en tableaux d'objet
    $lesParametres = new stdClass();
    $lesParametres->data = $select->fetchAll(PDO::FETCH_OBJ);
    $lesParametres->nombre = count($lesParametres->data);

    $object_retour->data["resultats"] = $lesParametres;
}else{
    $error = 'Erreur SQL:'.print_r($select->errorInfo(), true)." (".$strSql.")";
    $object_retour->strErreur = $error;
}
$select->closeCursor();

//--------------------------------------------------------------------------
if($modeDebug){
    $strSorti .= ($strSql);
}

//Cloture de l'interface
require("../API/php/footer.php");
?>